#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class ContactDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new Contact instance filled with the DataReader's current record data 
	     /// </summary>
	     private Contact GetContactFromReader(IDataReader dr)
	     {
		     Contact obj = new Contact();
		     obj.ContactID = (dr["ContactID"] is DBNull) ? Int32.MinValue :  (Int32)dr["ContactID"];
		     obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue :  (Int32)dr["CateID"];
		     obj.FullName = (dr["FullName"] is DBNull) ? String.Empty :  (String)dr["FullName"];
		     obj.Address = (dr["Address"] is DBNull) ? String.Empty :  (String)dr["Address"];
		     obj.Tel = (dr["Tel"] is DBNull) ? String.Empty :  (String)dr["Tel"];
		     obj.Email = (dr["Email"] is DBNull) ? String.Empty :  (String)dr["Email"];
		     obj.Title = (dr["Title"] is DBNull) ? String.Empty :  (String)dr["Title"];
		     obj.Content = (dr["Content"] is DBNull) ? String.Empty :  (String)dr["Content"];
		     obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue :  (DateTime)dr["AddedDate"];
		     obj.ViewStatus = (dr["ViewStatus"] is DBNull) ? Int32.MinValue :  (Int32)dr["ViewStatus"];
		     return obj;
	     }
	     #endregion


	     /// <summary>
	     /// Returns a collection of Contact with the data read from the input DataReader
	     /// </summary>
	     private List<Contact> GetContactCollectionFromReader(IDataReader dr)
	     {
		     List<Contact> obj = new List<Contact>();
		     while(dr.Read())
			     obj.Add(GetContactFromReader(dr));
		     return obj;
	     }

	     public int Insert(Contact obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Direction = ParameterDirection.Output;
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
		     myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
		     myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
		     myCommand.Parameters.Add("@Tel", SqlDbType.VarChar).Value = obj.Tel;
		     myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@ViewStatus", SqlDbType.Int).Value = obj.ViewStatus;

		     int ret = myCommand.ExecuteNonQuery();
		     return (int)myCommand.Parameters["@ContactID"].Value; 
		     }
	     }

	     public bool Update(Contact obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_Update", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Value = obj.ContactID;
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
		     myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
		     myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
		     myCommand.Parameters.Add("@Tel", SqlDbType.VarChar).Value = obj.Tel;
		     myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@ViewStatus", SqlDbType.Int).Value = obj.ViewStatus;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     public bool Delete(Int32 ContactID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the Job_Contact with the specified ID
	     /// </summary>
	     public Contact GetContactByID(Int32 ContactID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_GetContactByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetContactFromReader(dr) : null);
		     }
	     }

	     //Get All Contact
	     public List<Contact> GetAllContact(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_GetAllContact", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetContactCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  Contact
	     public int GetCountContact()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_GetCountContact", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	     // Get ContactByCateID
	     public List<Contact> GetContactByCateID(Int32 CateID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_GetContactByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetContactCollectionFromReader(dr);
		     }
	     }

	     // Get ContactCountByCateID
	     public int GetContactCountByCateID(Int32 CateID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_Contact_GetContactCountByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	 }
}
